Relational database systems store data in tables organized by columns and rows. The tables typically are linked together by ‘relationships’ that simplify the storage of data and make complex queries against the database more efficient. Structured Query Language (or SQL) is a standardized language for creating and operating on relational databases.
A relational database may be organized or laid-out in a number of ways. In one type of database schema (i.e. layout), termed the ‘star’ schema, a central fact table is provided, which contains a compound primary key, with one segment for each “dimension” and with additional columns of additive, numeric facts. In the star schema, a number of dimension tables contain detailed information of the “dimensions” outlined in the fact table. Conceptually, the binding relationship between the fact table and the dimension tables forms a ‘star’-like shape. When an SQL query is made, generally the fact table is joined with relevant dimension tables using a join condition that represents the binding relationship.
A relational database system typically includes an ‘optimizer’ that plans the execution of SQL queries. For example, if a query requires accessing or ‘joining’ more than two tables, as is normally required when accessing tables in a star schema, the optimizer will select the order that the tables are joined to produce the requested result in the shortest period of time or to satisfy some other criteria.
Typically, the fact table in a star schema is very large. For example, many large retail chains may operate relational databases that contain daily sales figures. The table of daily sales figures is a typical fact table, which may include millions or billions of rows and a large number of columns. In order to make access to the fact table faster, a database administrator often defines an “index” on the fact table's primary key for queries defined on the star schema. In many situations, the best plan available to the optimizer is a “star join”. A star join firstly joins all dimension tables, then joins the result of the first join with the fact tables through the index on the fact table. However, in some cases, a dimension may be specified as an IN-List, which is utilized as a filter to disqualify rows while scanning the fact table. Since dimensions specified as an IN-List cannot participate in the join operation to the fact table, queries with dimension tables specified as IN-Lists cannot take advantage of the Star Join technique.